package nc.impl.hrpbm.deptbuget;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.stream.Collectors;

import nc.bs.dao.BaseDAO;
import nc.bs.hrpbm.deptbudget.DeptBudgetRevenueAndExpenditureDetailService;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.pub.hrpbm.util.HrpbmReportUtils;
import nc.pub.smart.context.SmartContext;
import nc.pub.smart.data.DataSet;
import nc.pub.smart.metadata.Field;
import nc.pub.smart.metadata.MetaData;
import nc.vo.pub.BusinessException;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nccloud.commons.lang.ArrayUtils;
import uap.lfw.bq.portal.base.StringUtils;

public class DeptBudgetRevenueAndExpenditureDetailServiceImpl implements DeptBudgetRevenueAndExpenditureDetailService{
    HrpbmReportUtils Utils = new HrpbmReportUtils();
    private BaseDAO baseDAO;

    public BaseDAO getBaseDAO() {
        return baseDAO == null ? new BaseDAO() : baseDAO;
    }

    public static final String cwbudgetdeptcode = "405"; // 财务部门编码
    public static final String firstparentcode = "1"; // 收支项目: 收入科目编码

    String[] fields = new String[] { "nyear", "depteconomicname", "fundsourcename", "governmenteconomicname", "f1project_name",
            "f2project_name", "type_name", "projectname","oneup_01","oneup_02","oneup_03","oneup_04","oneup_05" ,"amount"};
    String[] fieldCaptions = new String[] { "年度", "部门经济分类", "资金来源", "政府经济分类", "一级项目名称", "二级项目名称", "项目分类", "功能科目","财政基本拨款经费","财政项目拨款经费","科教经费","非同级财政拨款经费","其他经费","合计" };






    private Field[] initField() {
        Field[] FieldInfo = new Field[fields.length];
        for (int i = 0; i < fields.length; i++) {
            Field filed = new Field();
            filed.setCaption(fieldCaptions[i]);
            filed.setDbColumnType(Types.VARCHAR);
            if (i == 8 || i == 12|| i == 9|| i == 10|| i == 11|| i == 13) { // 5,7对应编制金额，批复金额，金额是否需要调整字段类型，根据情况定
                filed.setDbColumnType(Types.DECIMAL);
                filed.setPrecision(28);
                filed.setScale(2);
            }

            filed.setFldname(fields[i]);
//			if (i == 2 || i == 6 || i == 8) {// 视情况而定
//				filed.setPrecision(500);
//				filed.setDbColumnType(Types.VARCHAR);
//			}
            FieldInfo[i] = filed;
        }
        return FieldInfo;
    }
    /**
     * 	初始化metadata
     * @return
     */
    private MetaData initMetaData() {
        Field[] FieldInfo = initField();
        MetaData md = new MetaData();
        for (int i = 0; i < fields.length; i++) {
            md.addField(FieldInfo[i]);
        }
        return md;
    }
    public static DataSet buildDataSetobj(MetaData meta, List<Map<String, Object>> list, String[] fields) {
        if (ArrayUtils.isEmpty(fields)) {
            ExceptionUtils.wrappBusinessException("报表字段列不能为空，请检查！！！");
        }
        DataSet ds = null;
        if (null == list || list.size() == 0) {
            // 返回空数据集, 用于给语义模型加载空数据用
            Object[][] datas = new Object[1][fields.length];
            ds = new DataSet(meta, datas);
        } else {
            Object[][] datas = new Object[list.size()][fields.length];
            for(int i = 0;i < list.size();i++){
                for(int j = 0; j < fields.length; j++ ) {
                    datas[i][j] = list.get(i).get(fields[j].toString());
                    //vos[i].getAttributeValue(fields[j]);
                }
            }
            ds = new DataSet(meta, datas);
        }
        return ds;
    }

    public static String convertToInClause(String value) {
        if(StringUtils.isBlank(value)) {
            return "";
        }
        String[] values = value.split(",");

        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < values.length; i++) {
            sb.append('\'').append(values[i]).append('\'');
            if (i < values.length - 1) {
                sb.append(',');
            }
        }
        return sb.toString();
    }
    @Override
    public DataSet process(SmartContext context) throws BusinessException {

        MetaData metadata = this.initMetaData();
        Utils.handelContext(context);
        List<Map<String, Object>>  sqlResult=queryData(context);

        return buildDataSetobj(metadata, sqlResult, fields);





    }
    public List<Map<String,Object>> initListByfundsource(List<Map<String,Object>> list) {

        Map<String, List<Map<String, Object>>> groupedByCategoryAndType = list.stream()
                .collect(Collectors.groupingBy(
                        map -> map.get("nyear").toString() + "-" + map.get("depteconomicname").toString()
                                + "-"+map.get("governmenteconomicname")
                                + "-"+map.get("f1project_name")+ "-"+map.get("f2project_name")
                                + "-"+map.get("projectname")// 创建一个复合键
                ));

        List<Map<String,Object>> returnlist = new ArrayList();
        int i = 0;
        for (Entry<String, List<Map<String, Object>>> entry : groupedByCategoryAndType.entrySet()) {
            List<Map<String, Object>> value =  entry.getValue();
            Map<String,Object> map1 = new HashMap();

            map1.put("oneup_01",Double.valueOf(0));
            map1.put("oneup_02",Double.valueOf(0));
            map1.put("oneup_03",Double.valueOf(0));
            map1.put("oneup_04",Double.valueOf(0));
            map1.put("oneup_05",Double.valueOf(0));
            map1.putAll(value.get(0));
            Double amount =0.00;
            for(Map<String, Object> map:value) {

                amount += map.get("amount_firstup")==null?0.00:((Number) map.get("amount_firstup")).doubleValue();
                String sourcecode= map.get("fundsourcecode").toString();
                map1.put("oneup_"+sourcecode, map.get("amount_firstup")==null?0:map.get("amount_firstup"));




            }
            // map1.put("number", i++);
            map1.put("amount", amount);
            returnlist.add(map1);


        }
        return returnlist;











    }

    public List<Map<String, Object>> queryData(SmartContext context)
            throws BusinessException {
        String pk_eps = (String) context.getAttribute("pk_eps");
        String pk_pjappl = (String) context.getAttribute("pk_pjappl");
        String pk_project = (String) context.getAttribute("pk_project");
        String inoutlevel = (String) context.getAttribute("level");
        String pk_inoutclass = (String) context.getAttribute("pk_inoutclass");
        String nyear = (String) context.getAttribute("nyear");
        String isfund = (String) context.getAttribute("isfund");
        String f1project = (String) context.getAttribute("f1project");
        String f2project = (String) context.getAttribute("f2project");
        StringBuffer sql = new StringBuffer();
        sql.append("select sum(aa.amount_firstup) as amount_firstup,aa.depteconomicname ,aa.fundsourcecode ,aa.governmenteconomicname, ");
        sql.append("aa.f1project_name,aa.f2project_name,aa.nyear,aa.type_name,aa.projectname,aa.pk_project from (");
        sql.append("select DISTINCT detail.nyear,projectclass.eps_name type_name,");
        sql.append("code1|| '-'||fip_docview_b.name1 depteconomicname,fundsource.name fundsourcename,detail.amount_firstup,");
        sql.append("detail.pk_budgetreplydetail,code2|| '-'||fip_docview_b.name2 governmenteconomicname,");
        sql.append(" CASE  WHEN project.def1='1' THEN project.project_name ");
        sql.append( " WHEN f2project.def1='1' THEN f2project.project_name ELSE null END AS f1project_name,");
        sql.append(" CASE  WHEN project.def1='2' THEN project.project_name ");
        sql.append(" WHEN f2project.def1='2' THEN  f2project.project_name ") ;
        sql.append(" ELSE null");
        sql.append(" END AS f2project_name,");
        sql.append(" CASE  WHEN project.def1='1' THEN project.pk_project ");
        sql.append( " WHEN f2project.def1='1' THEN f2project.pk_project ELSE null END AS pk_f1project,");
        sql.append(" CASE  WHEN project.def1='2' THEN project.pk_project ");
        sql.append(" WHEN f2project.def1='2' THEN  f2project.pk_project ") ;
        sql.append(" ELSE null");
        sql.append(" END AS pk_f2project,fundsource.code fundsourcecode,");
        sql.append(" detail.pk_project,doc.code|| '-' ||doc.name projectname ");
        sql.append(" from hrpbm_budgetreplydetail detail INNER JOIN  ");
        sql.append(" (SELECT DISTINCT doc1.code code1,doc2.code code2,doc1.pk_defdoc pk_defdoc1,");
        sql.append(" doc1.name name1,factorvalue5 pk_inoutbusiclass,doc2.name name2 ,doc3.code code3 FROM fip_docview_b ");
        sql.append(" INNER JOIN fip_docview ON fip_docview_b.pk_classview = fip_docview.pk_classview ");
        sql.append(" INNER JOIN bd_defdoc doc1 ON fip_docview_b.factorvalue1 = doc1.pk_defdoc ");
        sql.append(" INNER JOIN bd_defdoc doc2 ON fip_docview_b.factorvalue2 = doc2.pk_defdoc ");
        sql.append(" INNER JOIN bd_defdoc doc3 ON fip_docview_b.factorvalue3 = doc3.pk_defdoc ");
        sql.append(" WHERE fip_docview.viewcode = 'YS001' ) ");
        sql.append(" fip_docview_b  ON detail.pk_inoutclass = fip_docview_b.pk_inoutbusiclass ");
        sql.append(" LEFT JOIN bd_fundsource  fundsource  ON fundsource.pk_fundsource=detail.pk_fundsource");
        sql.append(" LEFT JOIN hrppl_pjappl pjappl ON  pjappl.pk_pjappl = detail.pk_project");
        sql.append(" LEFT JOIN (select pk_defdoc,name,code from bd_defdoc where  pk_defdoclist = '1001YY100000000EMU3T' ) doc ON doc.pk_defdoc =  pjappl.functionsubject");
        sql.append(" LEFT JOIN bd_project project ON detail.pk_project =project.pk_project");
        sql.append(" LEFT JOIN pm_eps projectclass ON project.pk_eps = projectclass.pk_eps");
        sql.append(" LEFT JOIN bd_project f2project ON f2project.pk_project =project.pk_parentpro");
        sql.append(" WHERE 1=1 and detail.nyear='"+nyear+"'");
        if (StringUtils.isNotBlank(pk_project)) {
            String pk_projectInString = convertToInClause(pk_project);
            sql.append("   AND bd_project.pk_project in (" + pk_projectInString + ") \n");// 项目分类
        }
        if (StringUtils.isNotBlank(pk_inoutclass)) {
            String pk_inoutclassInString = convertToInClause(pk_inoutclass);
            sql.append("   AND hrpbm_budgetreplydetail.pk_inoutclass in (" + pk_inoutclassInString + ") \n");
        }

        if (StringUtils.isNotBlank(pk_pjappl)) {
            String pjappl = convertToInClause(pk_pjappl);
            sql.append("   AND doc.pk_defdoc in (" + pjappl + ") \n");// 功能科目
        }
        if(isfund!=null) {

            sql.append( " and fip_docview_b.code3 = '"+isfund+"'");

        }









        sql.append(") aa  WHERE 1=1");


        if (StringUtils.isNotBlank(f1project)) {
            String f1projectStr = convertToInClause(f1project);
            sql.append("   AND aa.pk_f1project in (" + f1projectStr + ") \n");// 预算科室
        }
        if (StringUtils.isNotBlank(f2project)) {
            String f2projectStr = convertToInClause(f2project);
            sql.append("   AND aa.pk_f2project in (" + f2projectStr + ") \n");// 预算科室
        }
        sql.append(" group by 	aa.depteconomicname,aa.pk_project, aa.fundsourcecode	,aa.governmenteconomicname,nyear,");

        sql.append("f1project_name,f2project_name,type_name,projectname");

        List<Map<String,Object>> list = (ArrayList<Map<String,Object>>)getBaseDAO().executeQuery(sql.toString(), new MapListProcessor());
        list =  this.recursiveQuery(list);
        if(list == null || list.isEmpty()) return null;
        //根据前7项进行分组
        List<Map<String,Object>> list1 = initListByfundsource(list);
        //行合计计算
        List<Map<String, Object>> originalList = new ArrayList<>();


//				if(f1project!=null||f2project!=null) {
//					originalList = filterMaps(list,f1project,f2project);
//				return 	originalList;
//				}
        return list1;
    }

    /**
     * 递归处理三级项目之前的一级二级项目
     * @param list
     * @return
     * @throws Exception
     */
    public List<Map<String,Object>> recursiveQuery(List<Map<String,Object>> list) throws BusinessException {
        if(list ==null || list.isEmpty()) return list;
        for (Map<String, Object> map : list) {
            Object pk_project = map.get("pk_project");
            if(pk_project != null){
                List<Map<String,Object>> o = (List<Map<String,Object>>)this.getBaseDAO().executeQuery(this.recursiveQuerySQL(pk_project), new MapListProcessor());
                if(o != null && !o.isEmpty()){
                    for (Map<String, Object> omap : o) {
                        if ("1".equals(omap.get("def1")+"")) {
                            map.put("f1project_name", omap.get("project_name"));
                        } else if ("2".equals(omap.get("def1")+"")) {
                            map.put("f2sproject_name", omap.get("project_name"));
                        }
                    }
                }
            }
        }
        return list;
    }
    public String recursiveQuerySQL (Object pk_project) throws BusinessException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT pk_project, def1, project_code, project_name\n" );
        sql.append("  FROM bd_project\n" );
        sql.append(" START WITH pk_project = '"+pk_project+"' \n" );
        sql.append("CONNECT BY PRIOR pk_parentpro = pk_project \n" );
        sql.append(" ORDER BY level DESC ");
        return sql.toString();
    }

    public static List<Map<String, Object>> filterMaps(List<Map<String, Object>> list,
                                                       String f1projectStr,
                                                       String f2projectStr
    ) {
        List<Map<String, Object>> filteredList = new ArrayList<>();

        // 将字符串转换为数组，如果字符串为空或null，则使用空数组
        String[] f1projects = f1projectStr == null || f1projectStr.isEmpty() ? new String[0] : f1projectStr.split(",");
        String[] f2projects = f2projectStr == null || f2projectStr.isEmpty() ? new String[0] : f2projectStr.split(",");


        for (Map<String, Object> map : list) {
            boolean matchesF1 = isMatch(map.get("pk_f1project"), f1projects);
            boolean matchesF2 = isMatch(map.get("pk_f2project"), f2projects);


// 注意：这里使用了逻辑与（&&）而不是或（||），如果你想要所有条件都满足时才添加，但如果任意条件满足就添加，则使用 ||
            if (( matchesF1) &&
                    (matchesF2) ) {
                filteredList.add(map);
            }
        }

        return filteredList;
    }

    private static boolean isMatch(Object mapValue, String[] values) {
        if (values.length == 0) {
            return true; // 如果没有过滤值，则认为总是匹配
        }

        if (mapValue instanceof String) {
            return Arrays.stream(values).anyMatch(value -> value.equals(mapValue));
        }

        return false; // 如果mapValue不是String，则不匹配
    }

}